/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAL;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Arrays;

/**
 *
 * @author Amaster
 */
public class StudentDAO {

    private Connection con;

    public StudentDAO(Connection con) {
        this.con = con;
    }

    public boolean insertStudent(Student st) throws SQLException {
        String insertQ = "insert into Stu values(?,?,?,?,?)";
        PreparedStatement pst = con.prepareStatement(insertQ);
        pst.setString(1, st.getId());
        pst.setString(2, st.getName());
        pst.setInt(3, st.getAge());
        pst.setString(4, st.getSex());
        pst.setString(5, st.getHometown());
        boolean kq = pst.execute();
        pst.close();
        return kq;
    }

    public String listAll(String query) throws SQLException {
        Statement st = con.createStatement();
        ResultSet rs = st.executeQuery(query);
        String output = "<table border='1' align = 'center' style=\"background-color: white\" >";
        output += "<tr>";
            
            
            output += "<th>" + "id" + "</th>";
            output += "<th>" + "name" + "</th>";
            output += "<th>" + "age" + "</th>";
            output += "<th>" + "sex" + "</th>";
            output += "<th>" + "hometown" + "</th>";
          
            output += "<th colspan='2'>" + "action" + "</th>";
            output += "</tr>";
        while (rs.next()) {
            output += "<tr>";
            String id = rs.getString(1);
            String name = rs.getString(2);
            int age = rs.getInt(3);
            String sex = rs.getString(4);
            String hometown = rs.getString(5);
            output += "<td>" + id + "</td>";
            output += "<td>" + name + "</td>";
            output += "<td>" + age + "</td>";
            output += "<td>" + sex + "</td>";
            output += "<td>" + hometown + "</td>";
            
            output += "<td><a href='del.jsp?id=" + id + "' onclick=\"return confirm(' " + "Do you want to delete this Student?" + "')\" style=\"text-decoration:none;\">Delete</a></td>";
            output += "<td style=\"border:1px solid; border-color: black;\"><a href=\"update.jsp?id=" + id + "\" style=\"text-decoration:none;\">Edit</a>&nbsp;</td>";
            output += "</tr>";
            
            
        }
        output += "</table>";
        st.close();
        return output;
    }
    
    public boolean edit(Student st, String change) throws SQLException {
        String insert = "UPDATE Stu SET id = ?, name = ? ,age = ?, sex = ? , hometown = ? WHERE id ='" + change + "'";
        PreparedStatement pst = con.prepareStatement(insert);
        pst.setString(1, st.getId());
        pst.setString(2, st.getName());
        pst.setInt(3, st.getAge());
        pst.setString(4, st.getSex());
        pst.setString(5, st.getHometown());
        boolean kq = pst.execute();
        pst.close();
        return kq;
    }

    public int checkUser(String query) throws SQLException {
        Statement st = con.createStatement();
        ResultSet rs = st.executeQuery(query);
        int output = 0;
        while (rs.next()) {


            String id = rs.getString(1);
            if (id != null) {
                return 1;

            }
        }

        st.close();
        return output;
    }

    public int checkPass(String query) throws SQLException {
        Statement st = con.createStatement();
        ResultSet rs = st.executeQuery(query);
        int output = 0;
        while (rs.next()) {


            String id = rs.getString(2);
            if (id != null) {
                return 1;

            }
        }

        st.close();
        return output;
    }
}
